package com.newegg.research;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DBConn
{
	private Connection conn;

	private Statement stat;

	private PreparedStatement pstat;

	// private static final String url =
	// "jdbc:sqlserver://10.16.77.142;databaseName=IPP3" ; // Database URL
	private static final String url = "jdbc:postgresql://10.16.77.142/portal";

	private static final String driver = "org.postgresql.Driver";

	private static final String username = "portal";

	private static final String password = "Portal123!@#";

	public DBConn()
	{
		try
		{
			// Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); //
			// load driver
			Class.forName(driver);
			conn = DriverManager.getConnection(url, username, password);
		} catch (ClassNotFoundException e)
		{
			System.out.println("Cann't found database driver\n");
			e.printStackTrace();
		} catch (SQLException e)
		{
			System.out.println("Cann't get connection:\n");
			e.printStackTrace();
		}
	}

	public ResultSet executeQuery(String sql)
	{
		try
		{
			stat = conn.createStatement();
			return stat.executeQuery(sql);
		} catch (Exception e)
		{
			System.err.println("Exception occured when query the sql:" + sql);
			e.printStackTrace();
			return null;
		}
	}

	public PreparedStatement getPreparedStatement(String sql)
			throws SQLException
	{
		return conn.prepareStatement(sql);
	}

	public void close()
	{
		if (pstat != null)
		{
			try
			{
				pstat.close();
			} catch (SQLException e)
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if (stat != null)
		{
			try
			{
				stat.close();
			} catch (SQLException e)
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if (conn != null)
		{
			try
			{
				conn.close();
			} catch (SQLException e)
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	// Test connection
	public static void main(String[] args) throws Exception
	{
		DBConn dbconn = new DBConn();
		ResultSet rs = dbconn
				.executeQuery("select count(*) from \"portal\".\"AppointedPageInfo3\"");
		rs.next();
		System.out.println(rs.getString(1));
		dbconn.close();
	}
}
